#! /bin/bash

HIVE_HOME=/usr/bin/hive

${HIVE_HOME} -S -e "


create database if not exists oe_dws;
use oe_dws;

drop table if exists oe_dws.dws_entry_daycount;
create table oe_dws.dws_entry_daycount(
       day string,
       month string,
       year string,
       --  维度
       origin_type string ,   --线上线下
       itcast_school_id string,
       itcast_subject_id string,
       origin_channel string,  --来源渠道
       consult_center string,   --咨询中心
       group_type string,  --分组标记
       -- 指标
        report_num bigint,    -- 报名人数
        intent_num  bigint,    -- 意向人数
        clue_num  bigint     -- 有效线索人数
)COMMENT '报名主题宽表'
row format delimited fields terminated by '\t'
stored as orc tblproperties ('orc.compress'='SNAPPY');
"

PRESTO_HOME=/export/server/presto/bin/presto


${PRESTO_HOME} --catalog hive --server hadoop01:8090 --execute "

insert into oe_dws.dws_entry_daycount
with tmp as (select id,
       substring (create_date_time,1,10) as day,
       update_date_time,
       customer_id,
       origin_type,
       creator,
       origin_channel,
       payment_state,
       payment_time,
       itcast_clazz_id,
       itcast_clazz_time,
       ems_student_id,
       total_fee,
       emp_id,
       real_name,
       tdepart_id,
       class_id,
       itcast_school_id,
       itcast_school_name,
       itcast_subject_id,
       itcast_subject_name,
       depart_id,
       name,
       customer_relationship_id,
       customer_relationship_first_id,
       appeal_status,
       dt
from oe_dwb.dwb_entry_detail)
select
      day,
      substring (day,1,7) as month,
      substring (day,1,4) as year,
      origin_type,
      itcast_school_id,
      itcast_subject_id,
      origin_channel,
      name as consult_center,
      case when grouping(itcast_school_id) = 0 and grouping(itcast_subject_id) = 0 then 'school_subject'
         --  when grouping(origin_type) =1 and grouping(itcast_school_id) = 0 then 'no_origin_school'
           when grouping(itcast_subject_id) = 0 then 'subject'
           when grouping(origin_type) = 1 then 'no_origin_school'
           when grouping(itcast_school_id) = 0  then 'school'
           when grouping(origin_channel) = 0 then 'channel'
           when grouping(name) = 0 then 'consult_center'
           when grouping(origin_type) = 0 then 'origin_type'
           else 'other' end as group_type,
           --else 'no_origin_school' end as group_type,
      case when grouping(itcast_school_id) = 0 and grouping(itcast_subject_id) = 0 then count(if(payment_state = 'PAID',1,null))
         --  when grouping(origin_type) =1 and grouping(itcast_school_id) = 0 then 'no_origin_school'
           when grouping(itcast_subject_id) = 0 then count(if(payment_state = 'PAID',1,null))
           when grouping(origin_type) = 1 then count(if(payment_state = 'PAID',1,null))
           when grouping(itcast_school_id) = 0  then count(if(payment_state = 'PAID', 1, null))
           when grouping(origin_channel) = 0 then count(if(payment_state = 'PAID',1,null))
           when grouping(name) = 0 then count(if(payment_state = 'PAID',1,null))
           else 0 end as report_num,
           count(id) as intent_num,
           count(if(appeal_status = 'valid' and origin_type = 'online',1,null)) as clue_num

from tmp
group by
grouping sets (
    (day,itcast_school_id),
    (day,origin_type,itcast_school_id),
    (day,origin_type,itcast_subject_id),
    (day,origin_type,itcast_school_id,itcast_subject_id),
    (day,origin_type,origin_channel),
    (day,origin_type,name),
    (day,origin_type)
)


"